Drew Dominguez - Professional PortfolioVB.NET Application
This was a class project for my Business Application Development course. We were working for a company called iAudio, a fictitious retail store that sells and installs audio equipment. The application created a table in Microsoft Excel and used it as input to perform a variety of functions, such as calculate the combined total sales for a given year and save the result in another Excel worksheet, identify any number of stores whose total sales fell below a given amount, and insert these calculations using loops into a report for each store in separate Microsoft Word documents with a specific format.
Sample Output
VB.NET Code
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim myExcelApp As Excel.Application
Dim myExcelWB As Excel.Workbook
Dim myExcelWorksheet1 As Excel.Worksheet
Dim myExcelWorksheet2 As Excel.Worksheet
Dim myExcelWorksheet3 As Excel.Worksheet
Dim myExcelWorksheet4 As Excel.Worksheet
Dim myExcelWorksheet5 As Excel.Worksheet
Dim myExcelWorksheet6 As Excel.Worksheet
myExcelApp = New Excel.Application()
myExcelWB = myExcelApp.Workbooks.Open("392Project.xlsx")
myExcelWorksheet1 = myExcelWB.Sheets(1)
myExcelWorksheet2 = myExcelWB.Sheets(2)
myExcelWorksheet3 = myExcelWB.Sheets(3)
myExcelWorksheet4 = myExcelWB.Sheets(4)
myExcelWorksheet5 = myExcelWB.Sheets(5)
myExcelWorksheet6 = myExcelWB.Sheets(6)
myExcelApp.Visible = True
myExcelWB.Activate()
myExcelWorksheet1.Cells(3, 5).value = "=SUM(B3:D3)"
myExcelWorksheet1.Cells(4, 5).value = "=SUM(B4:D4)"
myExcelWorksheet1.Cells(5, 5).value = "=SUM(B5:D5)"
myExcelWorksheet1.Cells(6, 5).value = "=SUM(B6:D6)"
myExcelWorksheet2.Cells(1, 1).value = "Total Sales"
myExcelWorksheet2.Range("A1").Font.Bold = True
myExcelWorksheet2.Cells(2, 1).value = myExcelWorksheet1.Cells(3, 5).value
myExcelWorksheet2.Cells(3, 1).value = myExcelWorksheet1.Cells(4, 5).value
myExcelWorksheet2.Cells(4, 1).value = myExcelWorksheet1.Cells(5, 5).value
myExcelWorksheet2.Cells(5, 1).value = myExcelWorksheet1.Cells(6, 5).value
myExcelWorksheet1.Cells(3, 6).value = "=AVERAGE(B3:D3)"
myExcelWorksheet1.Cells(4, 6).value = "=AVERAGE(B4:D4)"
myExcelWorksheet1.Cells(5, 6).value = "=AVERAGE(B5:D5)"
myExcelWorksheet1.Cells(6, 6).value = "=AVERAGE(B6:D6)"
myExcelWorksheet3.Cells(1, 1).value = "Average Sales"
myExcelWorksheet3.Range("A1").Font.Bold = True
myExcelWorksheet3.Cells(2, 1).value = myExcelWorksheet1.Cells(3, 6).value
myExcelWorksheet3.Cells(3, 1).value = myExcelWorksheet1.Cells(4, 6).value
myExcelWorksheet3.Cells(4, 1).value = myExcelWorksheet1.Cells(5, 6).value
myExcelWorksheet3.Cells(5, 1).value = myExcelWorksheet1.Cells(6, 6).value
Dim totalArray() As Double = {myExcelWorksheet2.Cells(2, 1).value, myExcelWorksheet2.Cells(3, 1).value, myExcelWorksheet2.Cells(4, 1).value, myExcelWorksheet2.Cells(5, 1).value}
Dim counter As Double
Dim AutoAverage As Double
Dim HomeAverage As Double
Dim PersonalAverage As Double
If totalArray(0) < 8 Then
myExcelWorksheet4.Cells(2, 1).value = myExcelWorksheet1.Cells(3, 1).value
counter = counter + 1
AutoAverage = AutoAverage + myExcelWorksheet1.Cells(3, 2).value
HomeAverage = HomeAverage + myExcelWorksheet1.Cells(3, 3).value
PersonalAverage = PersonalAverage + myExcelWorksheet1.Cells(3, 4).value
End If
If totalArray(1) < 8 Then
myExcelWorksheet4.Cells(3, 1).value = myExcelWorksheet1.Cells(4, 1).value
counter = counter + 1
AutoAverage = AutoAverage + myExcelWorksheet1.Cells(4, 2).value
HomeAverage = HomeAverage + myExcelWorksheet1.Cells(4, 3).value
PersonalAverage = PersonalAverage + myExcelWorksheet1.Cells(4, 4).value
End If
If totalArray(2) < 8 Then
myExcelWorksheet4.Cells(4, 1).value = myExcelWorksheet1.Cells(5, 1).value
counter = counter + 1
AutoAverage = AutoAverage + myExcelWorksheet1.Cells(5, 2).value
HomeAverage = HomeAverage + myExcelWorksheet1.Cells(5, 3).value
PersonalAverage = PersonalAverage + myExcelWorksheet1.Cells(5, 4).value
End If
If totalArray(3) < 8 Then
myExcelWorksheet4.Cells(5, 1).value = myExcelWorksheet1.Cells(6, 1).value
counter = counter + 1
AutoAverage = AutoAverage + myExcelWorksheet1.Cells(6, 2).value
HomeAverage = HomeAverage + myExcelWorksheet1.Cells(6, 3).value
PersonalAverage = PersonalAverage + myExcelWorksheet1.Cells(6, 4).value
End If
myExcelWorksheet4.Cells(7, 1).value = counter
myExcelWorksheet5.Cells(2, 1).value = AutoAverage / counter
myExcelWorksheet5.Cells(2, 2).value = HomeAverage / counter
myExcelWorksheet5.Cells(2, 3).value = PersonalAverage / counter
'WORD DOCUMENT///////////////////////////////////////////////////////////////////////////////////////////////
Dim myWordApp As Word.Application
Dim myWordDoc As Word.Document
Dim myTable As Word.Table
Dim myTable2 As Word.Table
Dim Now As DateTime = DateTime.Now
Dim myParagraph1 As Word.Paragraph
Dim myParagraph2 As Word.Paragraph
Dim i As Integer
i = 0
Dim managerName(3) As String
Dim streetAddress(3) As String
Dim city(3) As String
Dim sppc(3) As String
Dim country(3) As String
Dim auto(3) As Double
Dim home(3) As Double
Dim personal(3) As Double
i = 0
While i < 4
managerName(i) = myExcelWorksheet6.Cells(i + 2, 1).value
streetAddress(i) = myExcelWorksheet6.Cells(i + 2, 2).value
city(i) = myExcelWorksheet6.Cells(i + 2, 3).value
sppc(i) = myExcelWorksheet6.Cells(i + 2, 4).value
country(i) = myExcelWorksheet6.Cells(i + 2, 5).value
auto(i) = myExcelWorksheet1.Cells(i + 3, 2).value
home(i) = myExcelWorksheet1.Cells(i + 3, 3).value
personal(i) = myExcelWorksheet1.Cells(i + 3, 4).value
i += 1
End While
i = 0
While i < 4
myWordApp = New Word.Application()
myWordDoc = myWordApp.Documents.Add()
myWordApp.Visible = True
Dim mysave() As String = {"BeverlyHills.docx", "London.docx", "NYC.docx", "Tokyo.docx"}
myWordDoc.SaveAs(mysave(i))
myWordApp.Selection.TypeParagraph()
myWordApp.Selection.TypeText(Now & vbNewLine & managerName(i) & vbVerticalTab & streetAddress(i) & vbVerticalTab & city(i) & ", " & sppc(i) & ", " & country(i) & vbNewLine & "Dear " & managerName(i) & "," & vbNewLine)
myWordApp.Selection.Range.Text =
("I have been asked by Mr. Smithson, the corporate sales director, to send you a report from the sales statistics collected from the 2010 fiscal year. The first table listed below is the information specific to your store.")
myTable = myWordDoc.Tables.Add(myWordDoc.Bookmarks.Item("\endofdoc").Range, 4, 2)
myTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
myTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
Dim myArray() As String = {city(i), "Sales- Automotive", "Sales- Home", "Sales- Personal"}
For j As Integer = 0 To 3
myTable.Cell(j + 1, 1).Range.Text = myArray(j)
Next
Dim dataArray() As Double = {auto(i), home(i), personal(i)}
For k As Integer = 0 To 2
myTable.Cell(k + 2, 2).Range.Text = dataArray(k)
Next
'Dim dataArray1() As Double = {LondonSales
myParagraph1 = myWordDoc.Content.Paragraphs.Add(myWordDoc.Bookmarks.Item("\endofdoc").Range)
myParagraph1.Range.InsertParagraphBefore()
myParagraph1.Range.Text = _
("Out of our four retail stores" & " " & counter & " " & "failed to meet the corporate sales target of $15 million. These stores are: " & myExcelWorksheet4.Cells(2, 1).value & ", " & myExcelWorksheet4.Cells(3, 1).value & ". The average sales from these underperforming stores are shown in the following tables.")
myParagraph1.Range.InsertParagraphAfter()
myTable2 = myWordDoc.Tables.Add(myWordDoc.Bookmarks.Item("\endofdoc").Range, 4, 2)
myTable2.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
myTable2.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
Dim myArray2() As String = {"Average sales from underperforming stores", "Automotive", "Home", "Personal"}
For j As Integer = 0 To 3
myTable2.Cell(j + 1, 1).Range.Text = myArray2(j)
Next
Dim underArray() As Double = {myExcelWorksheet5.Cells(2, 1).value, myExcelWorksheet5.Cells(2, 2).value, myExcelWorksheet5.Cells(2, 3).value}
For k As Integer = 0 To 2
myTable2.Cell(k + 2, 2).Range.Text = underArray(k)
Next
myParagraph2 = myWordDoc.Content.Paragraphs.Add(myWordDoc.Bookmarks.Item("\endofdoc").Range)
myParagraph2.Range.InsertParagraphBefore()
myParagraph2.Range.Text = ("Please contact me at so-and-so@iaudio.com if you have any questions. Thank you for your hard work. See you at the corporate retreat in Jamaica in January!" & vbNewLine & "Sincerely," & vbVerticalTab & "So-and-So T.Johnson" & vbVerticalTab & "Business Analyst")
myParagraph2.Range.InsertParagraphAfter()
i += 1
myTable.Cell(1, 1).Range.Font.Bold = True
myTable2.Cell(1, 1).Range.Font.Bold = True
End While
End Sub
End Class